Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# Scalar Subquery Expressio ns

A scalar subquery expression is a subquery that returns exactly one column value from one row. The value of the scalar subquery expression is the value of the select list item of the subquery. If the subquery returns 0 rows, then the value of the scalar subquery expression is `NULL`. If the subquery returns more than one row, then Oracle returns an error.

You can use a scalar subquery expression in most syntax that calls for an expression (`expr`). However, scalar subqueries are not valid expressions in the following places:

- As default values for columns

- As hash expressions for clusters

- In the `RETURNING` clause of DML statements

- As the basis of a function-based index

- In `CHECK` constraints

- In `WHEN` conditions of `CASE` expressions

- In `GROUP` `BY` and `HAVING` clauses

- In `START` `WITH` and `CONNECT` `BY` clauses

- In statements that are unrelated to queries, such as `CREATE` `PROFILE`